﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using System.Data.SqlClient;

namespace 附加数据库
{
    public partial class MainFrm : Form
    {
        SqlConnection con = new SqlConnection();
        public MainFrm()
        {
            InitializeComponent();
            Enabled(false);
        }
        //添加数据库文件
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog1 = new OpenFileDialog();
            fileDialog1.Filter = "文件类型(*.mdf,*.mdf,*.mdf)|*.mdf;*.mdf;*.mdf|All files (*.*)|*.*";
            fileDialog1.FilterIndex = 1;
            fileDialog1.RestoreDirectory = true;
            if (fileDialog1.ShowDialog() == DialogResult.OK)
            {
                this.listBox1.Items.Clear();
                string FileName = Path.GetFileNameWithoutExtension(fileDialog1.FileName);
                this.txt_dbName.Text = FileName.LastIndexOf("_") == -1 ? FileName : FileName.Substring(0, FileName.LastIndexOf("_"));
                this.listBox1.Items.Add(fileDialog1.FileName);
                AddOrLdf(fileDialog1.FileName);
                Enabled(true);
            }
            else
            {
                this.listBox1.Items.Clear();
                Enabled(false);
            }
        }
        public void AddOrLdf(string path)
        {
            string temp = "", a = "", b = "";
            int last = path.LastIndexOf("\\");
            temp = path.Remove(last);
            a = temp + "\\" + this.txt_dbName.Text + ".ldf";
            if (File.Exists(a))
            {
                this.listBox1.Items.Add(a);
            }
            else
            {
                b = temp + "\\" + this.txt_dbName.Text + "_log.ldf";
                if (File.Exists(b))
                {
                    this.listBox1.Items.Add(b);
                }
                else
                {
                    b = temp + "\\" + this.txt_dbName.Text + "log.ldf";
                    if (File.Exists(b))
                    {
                        this.listBox1.Items.Add(b);
                    }
                    else
                    {
                        MessageBox.Show("没有数据库日志文件！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        this.listBox1.Items.Clear();
                        this.txt_dbName.Text = "";
                        Enabled(false);
                    }
                }
            }
        }
        //开启SqlServer服务器
        private void button3_Click(object sender, EventArgs e)
        {
            MessageBox.Show(ExectureCMD("net start mssqlserver"));
        }
        //执行CMD命令
        public static string ExectureCMD(string cmd_Text)
        {
            Process p = new Process();
            p.StartInfo.FileName = "cmd.exe";
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardError = true;
            p.StartInfo.RedirectStandardInput = true;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.CreateNoWindow = true;
            p.Start();
            p.StandardInput.WriteLine(cmd_Text);
            p.StandardInput.WriteLine("exit");

            return p.StandardOutput.ReadToEnd() + "\n" + p.StandardError.ReadToEnd();
        }
        private void button2_Click(object sender, EventArgs e)
        {
            if (this.listBox1.Items.Count > 0)
            {
                try
                {
                    if (string.IsNullOrEmpty(this.txt_sapwd.Text))
                        return;
                    this.con.ConnectionString = "server=.;uid=sa;pwd=" + this.txt_sapwd.Text + ";database=master";
                    this.con.Open();
                    SqlCommand cmd = new SqlCommand("sp_attach_db", this.con);

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@dbname", this.txt_dbName.Text);
                    for (int i = 0; i < this.listBox1.Items.Count; i++)
                        cmd.Parameters.AddWithValue("@filename" + (i + 1), this.listBox1.Items[i]);

                    cmd.ExecuteNonQuery();
                    MessageBox.Show("OK，恭喜您，附加完成！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                this.con.Close();
            }
        }
        private void button4_Click(object sender, EventArgs e)
        {
            if (this.txt_dbName.Text.Trim() == "")
                return;
            if (MessageBox.Show("您真的要从当前SQL服务器中删除数据库" + this.txt_dbName.Text + "吗？", "系统提示：", MessageBoxButtons.YesNo) == DialogResult.No)
                return;
            try
            {
                this.con.ConnectionString = "server=.;uid=sa;pwd=" + this.txt_sapwd.Text + ";database=master";
                this.con.Open();
                SqlCommand cmd = new SqlCommand("use master;drop database " + this.txt_dbName.Text, this.con);

                cmd.ExecuteNonQuery();

                MessageBox.Show("OK，恭喜您，删除成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void button5_Click(object sender, EventArgs e)
        {
            if (this.txt_dbName.Text.Length > 0)
            {
                try
                {
                    if (string.IsNullOrEmpty(this.txt_sapwd.Text))
                    {
                        MessageBox.Show("数据库密码不能为空！");
                        return;
                    }
                    this.con.ConnectionString = "server=.;uid=sa;pwd=" + this.txt_sapwd.Text + ";database=master";
                    this.con.Open();
                    // EXEC sp_detach_db @dbName, 'true' 
                    SqlCommand cmd = new SqlCommand("sp_detach_db", this.con);

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@dbname", this.txt_dbName.Text);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("OK，恭喜您，分离完成！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                this.con.Close();
            }
        }
        private void button6_Click(object sender, EventArgs e)
        {
            if (this.txt_dbName.Text.Length > 0)
            {
                try
                {
                    if (string.IsNullOrEmpty(this.txt_sapwd.Text))
                    {
                        MessageBox.Show("数据库密码不能为空！");
                        return;
                    }
                    FolderBrowserDialog dialog = new FolderBrowserDialog();
                    if (dialog.ShowDialog(this) == DialogResult.OK)
                    {
                        string date = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() +
                            DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
                        string file = dialog.SelectedPath + "\\" + txt_dbName.Text.ToLower() + "_backup_" + date + ".bak";
                        this.con.ConnectionString = "server=.;uid=sa;pwd=" + this.txt_sapwd.Text + ";database=master";
                        this.con.Open();

                        string sqlString = "backup database  " + txt_dbName.Text.ToLower() + " to disk = '" + file + "' WITH FORMAT, NAME = '" +
                            txt_dbName.Text.ToLower() + "'";
                        // EXEC sp_detach_db @dbName, 'true' 
                        SqlCommand cmd = new SqlCommand(sqlString, this.con);
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("OK，恭喜您，数据库备份完成！");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                this.con.Close();
            }
        }
        private void listBox1_DragDrop(object sender, DragEventArgs e)
        {
            string path = ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();
            string temp = "", a = "", b = "";
            string Exten = Path.GetExtension(path).ToLower();
            this.listBox1.Items.Clear();
            if (Exten == ".mdf")
            {
                string FileName = Path.GetFileNameWithoutExtension(path);
                this.txt_dbName.Text = FileName.LastIndexOf("_Data") == -1 ? FileName : FileName.Substring(0, FileName.LastIndexOf("_"));
                this.listBox1.Items.Add(path);
                AddOrLdf(path);
            }
          
            #region MyRegion
            //ListBox listbox = (ListBox)sender;
            //string path= ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();
            //string temp = "",a="",b="";
            //string Exten = Path.GetExtension(path).ToLower();
            //this.listBox1.Items.Clear();
            //if (Exten == ".mdf")
            //{
            //    string FileName = Path.GetFileNameWithoutExtension(path);
            //    this.txt_dbName.Text = FileName;
            //    this.listBox1.Items.Add(path);
            //    int last = path.LastIndexOf("\\");
            //    temp = path.Remove(last);
            //    a = temp + "\\" + this.txt_dbName.Text + ".ldf";
            //    if (File.Exists(a)){
            //        this.listBox1.Items.Add(a);
            //    }else{
            //        b = temp + "\\" + this.txt_dbName.Text + "_log.ldf";
            //        if (File.Exists(b)){
            //            this.listBox1.Items.Add(b);
            //        }else{
            //            MessageBox.Show("没有数据库日志文件！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            //            this.listBox1.Items.Clear();
            //            this.txt_dbName.Text = "";
            //            Enabled(false);
            //            return;
            //        }
            //    }
            //}
            //else if (Exten == ".ldf")
            //{
            //    string FileName = Path.GetFileNameWithoutExtension(path);
            //    this.txt_dbName.Text = FileName.LastIndexOf("_") == -1 ? FileName : FileName.Substring(0, FileName.LastIndexOf("_"));
            //    this.listBox1.Items.Add(path);
            //    int last = path.LastIndexOf("\\");
            //    temp = path.Remove(last);
            //    a = temp + "\\" + this.txt_dbName.Text + ".mdf";
            //    if (File.Exists(a)){
            //        this.listBox1.Items.Add(a);
            //    }else{
            //        MessageBox.Show("没有数据库文件！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            //        this.listBox1.Items.Clear();
            //        this.txt_dbName.Text = "";
            //        Enabled(false);
            //        return;
            //    }
            //}else{
            //    MessageBox.Show("不是数据库文件，仅支持mdf和ldf文件！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            //    this.listBox1.Items.Clear();
            //    this.txt_dbName.Text = "";
            //    Enabled(false);
            //    return;
            //}

            #endregion

            Enabled(true);
        }
        private void listBox1_DragEnter(object sender, DragEventArgs e)
        {
            //判断是不是可以接收的数据类型   
            if (e.Data.GetDataPresent(DataFormats.FileDrop))
                e.Effect = DragDropEffects.Link;
            else e.Effect = DragDropEffects.None;
        }
        public void Enabled(bool flg)
        {
            if (flg == true)
            {
                button2.Enabled = true;
                button3.Enabled = true;
                button4.Enabled = true;
                button5.Enabled = true;
                button6.Enabled = true;
            }
            else
            {
                button2.Enabled = false;
                button3.Enabled = false;
                button4.Enabled = false;
                button5.Enabled = false;
                button6.Enabled = false;
            }
        }

        private void listBox1_PreviewKeyDown(object sender, PreviewKeyDownEventArgs e)
        {
            if (e.KeyCode == Keys.F1)
            {
                MessageBox.Show(HelpText, "帮助");
            }
        }
        public static string HelpText
        {
            get
            {
                StringBuilder list = new StringBuilder();
                list.AppendLine("目前支持的功能：☠");
                list.AppendLine("       ");
                list.AppendLine("根据“数据库名”来搜索本文件下对应的日志文件:  ");
                list.AppendLine("      数据库名.ldf ");
                list.AppendLine("      数据库名_log.ldf  ");
                list.AppendLine("      数据库名log.ldf  ");
                list.AppendLine("       ");
                list.AppendLine("数据库名的读取: ");
                list.AppendLine("      数据库名_Data.MDF  ");
                list.AppendLine("      数据库名.mdf  ");
                list.AppendLine("       ");
                return list.ToString();
            }
        }
    }
}
